package kr.or.kead.service;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import kr.or.kead.domain.InfoStudent;
import kr.or.kead.utils.MySqlCon;



public class DaoInfoStudent implements DaoTable {
	
	@Override
	public int insertDao(Object obj) {
		Connection con = MySqlCon.getConn();
		
		InfoStudent std = (InfoStudent) obj;
		String sql = "insert into infostudent"
				+ "(stdname,juminNum,startdate,enddate,mobile,tel,stdaddr,roomnum,stdtype,grade,email,departCode)"
				+ " values(?,?,?,?,?,?,?,?,?,?,?,?)";
		PreparedStatement pstmt=null;
		try {
			pstmt=con.prepareStatement(sql);
			pstmt.setString(1, std.getStdName());
			pstmt.setString(2, std.getJuminNum());
			pstmt.setDate(3, new Date(std.getStartDate().getTime()));
			pstmt.setDate(4, new Date(std.getEndDate().getTime()));
			pstmt.setString(5, std.getMobile());
			pstmt.setString(6, std.getTel());
			pstmt.setString(7, std.getStdAddr());
			pstmt.setInt(8, std.getRoomNum());
			pstmt.setInt(9, std.getStdType());
			pstmt.setInt(10, std.getGrade());
			pstmt.setString(11, std.getEmail());
			pstmt.setInt(12, std.getDepart());
			pstmt.execute();
		} catch (SQLException e) {
			return -1;
		} finally {
			try {
				pstmt.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 0;
	}

	@Override
	public int updateDao(Object obj) {
		Connection con = MySqlCon.getConn();
		
		InfoStudent std = (InfoStudent) obj;
		String sql = "update infostudent set "
				+ "stdname=?,"
				+ "juminnum=?,"
				+ "startdate=?,"
				+ "enddate=?,"
				+ "mobile=?,"
				+ "tel=?,"
				+ "stdAddr=?,"
				+ "roomnum=?,"
				+ "stdtype=?,"
				+ "grade=?,"
				+ "email=?,"
				+ "departcode=?"
				+ " where idx=?";
		
		PreparedStatement pstmt=null;
		try {
			pstmt=con.prepareStatement(sql);
			pstmt.setString(1, std.getStdName());
			pstmt.setString(2, std.getJuminNum());
			pstmt.setDate(3, new Date(std.getStartDate().getTime()));
			pstmt.setDate(4, new Date(std.getEndDate().getTime()));
			pstmt.setString(5, std.getMobile());
			pstmt.setString(6, std.getTel());
			pstmt.setString(7, std.getStdAddr());
			pstmt.setInt(8, std.getRoomNum());
			pstmt.setInt(9, std.getStdType());
			pstmt.setInt(10, std.getGrade());
			pstmt.setString(11, std.getEmail());
			pstmt.setInt(12, std.getDepart());
			pstmt.setInt(13, std.getIdx());
			System.out.println(pstmt);
			pstmt.execute();
		} catch (SQLException e) {
			return -1;
		} finally {
			try {
				pstmt.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 0;
	}

	@Override
	public int deleteDao(int regNo) {
		Connection con = MySqlCon.getConn();
		
		String sql = "delete from infostudent where idx=?";
		PreparedStatement pstmt=null;
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, regNo);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			return -1;
		}
		return 0;
	}

	@Override
	public ArrayList<Object> selectDao() {
		Connection con = MySqlCon.getConn();
		
		String sql = "select * from infostudent";
		ArrayList<Object> students = new ArrayList<>();
		PreparedStatement pstmt = null;
		try {
			pstmt = con.prepareStatement(sql);

			ResultSet rs = pstmt.executeQuery();
			InfoStudent std;
			while (rs.next()){
				std = new InfoStudent();
				std.setIdx(rs.getInt("idx"));
				std.setStdName(rs.getString("stdName"));
				std.setJuminNum(rs.getString("juminnum"));
				std.setStartDate(rs.getDate("startdate"));
				std.setEndDate(rs.getDate("enddate"));
				std.setMobile(rs.getString("mobile"));
				std.setTel(rs.getString("tel"));
				std.setStdAddr(rs.getString("stdaddr"));
				std.setRoomNum(rs.getInt("roomnum"));
				std.setStdType(rs.getInt("stdtype"));
				std.setGrade(rs.getInt("grade"));
				std.setEmail(rs.getString("email"));
				std.setDepart(rs.getInt("departcode"));
				students.add(std);
			}
			return students;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public InfoStudent selectTableById(int stdId) {
		Connection con = MySqlCon.getConn();
		
		String sql = "select * from infostudent where idx=?";
		try {
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, stdId);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()){
				InfoStudent std = new InfoStudent();
				std.setIdx(rs.getInt("idx"));
				std.setStdName(rs.getString("stdName"));
				std.setJuminNum(rs.getString("juminnum"));
				std.setStartDate(rs.getDate("startdate"));
				std.setEndDate(rs.getDate("enddate"));
				std.setMobile(rs.getString("mobile"));
				std.setTel(rs.getString("tel"));
				std.setStdAddr(rs.getString("stdaddr"));
				std.setRoomNum(rs.getInt("roomnum"));
				std.setStdType(rs.getInt("stdtype"));
				std.setGrade(rs.getInt("grade"));
				std.setEmail(rs.getString("email"));
				std.setDepart(rs.getInt("departcode"));
				return std;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public ArrayList<String> selectTableAllList() {
		Connection con = MySqlCon.getConn();
		
		String sql = "select idx, stdname from infostudent";
		ArrayList<String> lists = new ArrayList<>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while(rs.next()){
				lists.add(rs.getInt(1)+":"+rs.getString(2));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				pstmt.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}	
		
		
		return lists;
	}

	@Override
	public Object selectTableByEmail(String email) {
		Connection con = MySqlCon.getConn();		
		String sql = "select idx,stdname,juminnum,startdate,"
				+ "enddate,mobile,tel, stdaddr,roomnum,stdtype, grade, email, departCode "
				+ "from infostudent where email=?";
		PreparedStatement pstmt=null;
		ResultSet rs = null;
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, email);
			rs = pstmt.executeQuery();
			if (rs.next()){
				InfoStudent std = new InfoStudent();
				std.setIdx(rs.getInt("idx"));
				std.setStdName(rs.getString("stdName"));
				std.setJuminNum(rs.getString("juminnum"));
				std.setStartDate(rs.getDate("startdate"));
				std.setEndDate(rs.getDate("enddate"));
				std.setMobile(rs.getString("mobile"));
				std.setTel(rs.getString("tel"));
				std.setStdAddr(rs.getString("stdaddr"));
				std.setRoomNum(rs.getInt("roomnum"));
				std.setStdType(rs.getInt("stdtype"));
				std.setGrade(rs.getInt("grade"));
				std.setEmail(rs.getString("email"));
				std.setDepart(rs.getInt("departCode"));
				return std;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				pstmt.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}
	
	@Override
	public int updataTableByEmail(String pass, String email) {
		Connection con = MySqlCon.getConn();	
		String sql = "update infostudent set passwd=? where email=?";
		PreparedStatement pstmt=null;
		try {
			pstmt=con.prepareStatement(sql);
			pstmt.setString(1, pass);
			pstmt.setString(2, email);
			pstmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
			return -1;
		} finally {
			try {
				pstmt.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 0;
	}

	@Override
	public ArrayList<String> selectTableAllNames() {
		// TODO Auto-generated method stub
		return null;
	}

}
